package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaWhsOutQueryDTO;
import com.sunda.spmsoversea.entity.OverseaWhsOut;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 海外出库任务表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-09-22
 */
@Mapper
public interface OverseaWhsOutMapper extends BaseMapper<OverseaWhsOut> {

    @Select("<script>" +
            "SELECT OWO.AUTO_ID_WHS_OUT AS \"autoIdWhsOut\",\n" +
            "       OWO.UUID_WHS_OUT AS \"uuidWhsOut\",\n" +
            "       OWO.SPMS_ID AS \"spmsId\",\n" +
            "       OWO.CREATE_DATE AS \"createDate\",\n" +
            "       OWO.MOVEMENT_TYPE AS \"movementType\",\n" +
            "       OWO.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OWO.CREATE_USERID AS \"createUserid\",\n" +
            "       SU.LAST_NAME AS \"createUsername\",\n" +
            "       OWO.WERKS AS \"werks\",\n" +
            "       OWO.WORKSHOP_CODE AS \"workshopCode\",\n" +
            "       OWO.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OWO.RELATE_DOCUMENT AS \"relateDocument\",\n" +
            "       ORQ.COMMENTS AS \"overseaRequisitionSubject\",\n" +
            "       OWO.COST_CENTER AS \"costCenter\",\n" +
            "       OWO.COST_CENTER_NAME AS \"costCenterName\",\n" +
            "       OWO.DATA_VERSION AS \"dataVersion\",\n" +
            "       OWO.REMARKS AS \"remarks\",\n" +
            "       OWO.COMMENTS AS \"comments\",\n" +
            "       OWO.POSTING_DATE AS \"postingDate\",\n" +
            "       OWO.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       OWO.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       OWO.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       OWO.SAP_CANCEL_VOUCHER_NUMBER AS \"sapCencelVoucherNumber\",\n" +
            "       OWO.SAP_CANCEL_VOUCHER_YEAR AS \"sapCancelVoucherYear\",\n" +
            "       OWO.CREATE_TIME AS \"createTime\",\n" +
            "       OWO.UPDATE_TIME AS \"updateTime\"\n" +
            "FROM OVERSEA_WHS_OUT OWO\n" +
            "    LEFT JOIN SPMS_USER SU ON OWO.CREATE_USERID = SU.WORK_CODE " +
            "    LEFT JOIN OVERSEA_REQUISITION ORQ ON OWO.RELATE_DOCUMENT = ORQ.SPMS_ID \n" +
            "WHERE 1=1 \n" +
            " <when test='dto.uuidWhsOut!=null and dto.uuidWhsOut != \"\"'> AND OWO.UUID_WHS_OUT = #{dto.uuidWhsOut}</when>" +
            " <when test='dto.spmsId!=null and dto.spmsId != \"\"'> AND OWO.SPMS_ID = #{dto.spmsId}</when>" +
            " <when test='dto.relateDocument!=null and dto.relateDocument != \"\"'> AND OWO.RELATE_DOCUMENT = #{dto.relateDocument}</when>" +
            " <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND OWO.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            " <when test='dto.werks!=null and dto.werks != \"\"'> AND OWO.WERKS = #{dto.werks}</when>" +
            " <when test='dto.workshopCode!=null and dto.workshopCode != \"\"'> AND OWO.WORKSHOP_CODE = #{dto.workshopCode}</when>" +
            " <when test='dto.whsLocationCode!=null and dto.whsLocationCode != \"\"'> AND OWO.WHS_LOCATION_CODE = #{dto.whsLocationCode}</when>" +
            " <when test='dto.movementType!=null and dto.movementType != \"\"'> AND OWO.MOVEMENT_TYPE = #{dto.movementType}</when>" +
            " <when test='dto.beginDate!=null and dto.beginDate != \"\"'> AND OWO.CREATE_DATE &gt;= #{dto.beginDate}</when>" +
            " <when test='dto.endDate!=null and dto.endDate != \"\"'> AND OWO.CREATE_DATE &lt;= #{dto.endDate}</when>" +
            " <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND OWO.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            "  <when test='dto.createUser!=null and dto.createUser != \"\"'> " +
            "     AND INSTR(OWO.CREATE_USERID || ',' || SU.LAST_NAME, #{dto.createUser}, 1, 1) > 0 " +
            "  </when>" +
            "  <when test='dto.comments!=null and dto.comments != \"\"'> AND INSTR(OWO.COMMENTS, #{dto.comments}, 1, 1) > 0 </when>" +
            " ORDER BY OWO.SPMS_STATUS ASC, OWO.UPDATE_TIME DESC " +
            "</script>")
    List<Map<String, Object>> getWhsOutPage(IPage<OverseaWhsOut> page, @Param("dto") OverseaWhsOutQueryDTO dto);

    @Select("SELECT OWO.WHS_LOCATION_CODE AS \"lgort\",\n" +
            "       OWO.WERKS AS \"werks\",\n" +
            "       REPLACE(OWO.CREATE_DATE, '-', '') AS \"bldat\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"budat\",\n" +
            "       OWO.MOVEMENT_TYPE AS \"bwart\",\n" +
            "       OWO.SPMS_ID || OWO.REMARKS AS \"bktxt\"\n" +
            "FROM OVERSEA_WHS_OUT OWO\n" +
            "WHERE OWO.UUID_WHS_OUT = #{uuidWhsOut} ")
    Map<String, Object> dataHeadWhsOut(@Param("uuidWhsOut") String uuidWhsOut, @Param("postingDate") String postingDate);

    @Select("SELECT TO_CHAR(OWOD.ITEM) AS \"zeile\",\n" +
            "       OWOD.MATERIAL_NO AS \"matnr\",\n" +
            "       TO_CHAR(OWOD.ACTUAL_QTY_BASIC_UNIT) AS \"menge\",\n" +
            "       OWOD.BASIC_UNIT AS \"meins\",\n" +
            "       '' AS \"lifnr\",\n" +
            "       OWO.COST_CENTER AS \"kostl\",\n" +
            "       OWOD.REMARKS AS \"sgtxt\",\n" +
            "       OWOD.WERKS AS \"werks\",\n" +
            "       OWOD.WHS_LOCATION_CODE AS \"lgort\"\n" +
            "FROM OVERSEA_WHS_OUT_DTL OWOD\n" +
            "    LEFT JOIN OVERSEA_WHS_OUT OWO ON OWOD.UUID_WHS_OUT = OWO.UUID_WHS_OUT\n" +
            "WHERE OWOD.UUID_WHS_OUT = #{uuidWhsOut}\n" +
            "  AND OWOD.ACTUAL_QTY_BASIC_UNIT > 0\n" +
            "ORDER BY OWOD.ITEM ASC ")
    List<Map<String, Object>> dataItemWhsOut(@Param("uuidWhsOut") String uuidWhsOut);

    @Select("SELECT OWO.SAP_VOUCHER_NUMBER AS \"iMblnr\",\n" +
            "       OWO.SAP_VOUCHER_YEAR AS \"iMjahr\",\n" +
            "       REPLACE(#{cancelPostingDate}, '-', '') AS \"iBudat\"\n" +
            "FROM OVERSEA_WHS_OUT OWO\n" +
            "WHERE OWO.UUID_WHS_OUT = #{uuidWhsOut} ")
    Map<String, Object> dataHeadCancelWhsOut(@Param("uuidWhsOut") String uuidWhsOut,
                                             @Param("cancelPostingDate") String cancelPostingDate);
}
